
/****************************************************

LeaseDataForRegressions

inputs:

outputs:
	`"`pathout'LeaseDataForRegressions.dta"'
	
****************************************************/

clear
/*#delimit;*/
set mem 1000m
pause on
set more off


** PATHS

tempfile all prod lease permit property aaaa api docket leasedist space prod
tempfile leasefill competition operator wellpad wellpadid pipeline opdrill
tempfile dataquick corelogic tranval aaaaval price


local county = "Tarrant"
local shale = "Barnett"

********************************************************************************
* Oil and natural gas prices

use `"`pathprice'DailyPrice.dta"'
keep year month day px_last_wti18 px_last_ng18 pred_wti_vol18 pred_ng_vol18
rename year instyear
rename month instmonth
rename day instday

sort instyear instmonth instday
save `price',replace

********************************************************************************
* Proximity to pipeline

use `"`pathtad'AAAA_pipeline.dta"'

sort account_num
save `pipeline',replace

********************************************************************************
* Proximity to well and to lateral

use `"`pathtad'AAAA_wellpadID_04192018.dta"'

gen near_dist_wellpad_inv = 1/near_dist_wellpad
duplicates tag account_num wellpadID, gen(t)
drop if t > 0
drop t

sort account_num
save `wellpadid',replace

********************************************************************************
* Appraisal values

use `"`pathout'AppraisalValues_2003.dta"'

rename appraisal_year instyear
sort account_num instyear
save `aaaaval',replace

********************************************************************************
* Transaction values

use `"`pathout'TransactionValues_2003.dta"'

rename tranyear instyear
sort account_num instyear
save `tranval',replace

********************************************************************************
* Appraisals space & time

use `"`pathtad'AppraisalValues_SpaceTime.dta"'

sort account_num
save `space',replace

********************************************************************************
* TAD Attributes
********************************************************************************

use `"`pathtad'AAAAall.dta"'

drop pidn owner_crrt gis_link n1 n2 n3 fullmerge unique overlap_flag deed_date notice_date 
drop instrument appraisal_date appraisal_val from_accts land_val improve_val total_val year prop_class tad_map mapsco exemption merge2013 merge_parcel spec2 spec1 special_dist
duplicates drop account_num, force

sort account_num
merge account_num using `space', update
tab _merge
drop _merge

sort account_num
merge account_num using `pipeline',update
tab _merge
drop _merge

sort account_num
merge account_num using `wellpadid',update
tab _merge
drop _merge

sort account_num
save `aaaa',replace

********************************************************************************
* Merge issue and first production dates

*use `"`pathout'PermitDates.dta"'
use `"`pathout'PermitDates_04192018.dta"'

order wellpadnew
drop wellpadID
rename wellpadnew wellpadID

sort api
save `api',replace

********************************************************************************
* Instruments: Merge operator drilling attributes from 2003

use `"`pathout'OperatorDrilling_04192018.dta"'

rename operator firm_1

replace firm_1 = "arrington" if regexm(firm_1,"arrington")
replace firm_1 = "caffey" if regexm(firm_1,"caffey")
*replace firm_1 = "grande" if regexm(firm_1,"grand e")
replace firm_1 = "circle" if regexm(firm_1,"circle exp")
replace firm_1 = "cwc" if regexm(firm_1,"cwc ")
replace firm_1 = "kerr" if regexm(firm_1,"kerr ")
replace firm_1 = "prime" if regexm(firm_1,"prime ")
replace firm_1 = "range" if regexm(firm_1,"range ")
replace firm_1 = "tierra" if regexm(firm_1,"tierra ")
replace firm_1 = "woodstone" if regexm(firm_1,"woodstone ")

gen operatorDum = 1
gen operatorLarge = 1 if smallFirm == 0
drop firmNUMsm firmNUM
sort firm_1 -op_prod_wells
duplicates drop firm, force

sort firm_1
save `opdrill',replace

********************************************************************************
* Distance to TCC and Firm Offices

use `"`pathout'LeaseMergedToTCC.dta"'

replace grantor = lower(grantor)
replace grantee = lower(grantee)
replace grantor = itrim(trim(grantor))
replace grantee = itrim(trim(grantee))
replace record = lower(record)
replace record = subinstr(record," ","",.)
replace record = subinstr(record,regexs(0),"",.) if regexm(record,"[a-z]+")
replace record = substr(record,5,.) if regexm(record,"^200[0-9]") & length(record) == 13

sort record instdate
save `leasedist',replace

********************************************************************************
* Old instruments

tempfile permitabst nearbyInf

use `"`patharea'NearbyInfrastructure.dta"'

*rename abstHOLD abstract
sort abstract instyear instmonth
save `nearbyInf',replace

********************************************************************************

use `"`patharea'permitAbst.dta"'

rename abstnum abstract
destring abstract, replace

sort abstract instyear instmonth
save `permitabst', replace

gen tempmonth = instmonth - 1
gen tempyear = instyear
replace tempyear = tempyear - 1 if tempmonth ==0
replace tempmonth = 12 if tempmonth == 0
replace instmonth = tempmonth
replace instyear = tempyear
drop temp*

foreach var of varlist permitByAbst prodByAbst permitByAbst03 prodByAbst03 {
	rename `var' `var'_I
}

sort abstract instyear instmonth
merge abstract instyear instmonth using `permitabst', update
tab _merge
drop _merge


sort abstract instyear instmonth
save `permitabst',replace

gen tempmonth = instmonth - 1
gen tempyear = instyear
replace tempyear = tempyear - 1 if tempmonth ==0
replace tempmonth = 12 if tempmonth == 0
replace instmonth = tempmonth
replace instyear = tempyear
drop temp*

foreach var in permitByAbst prodByAbst permitByAbst03 prodByAbst03 {
	rename `var'_I `var'_II
}

sort abstract instyear instmonth
merge abstract instyear instmonth using `permitabst', update
tab _merge
drop _merge

sort abstract instyear instmonth
save `permitabst',replace

********************************************************************************
* Lease Competition
********************************************************************************

use `"`pathout'LeaseCompetitionAndProx_04202018.dta"'

sort account_num instyear instmonth
save `competition',replace


********************************************************************************
* Firm docket variables
********************************************************************************

tempfile docket_ct docket_sm docket

use `"`pathdocket'Docket_Ct.dta"'

replace county = "other" if county != "tarrant"
duplicates drop
*drop *hear*

foreach var of varlist rule37_yr rule38_yr rule3738_yr {
replace `var' = . if `var' == 0
sort fileyear `var'
by fileyear: carryforward `var',replace
gen `var'_noTar = 0
gsort fileyear -county
replace `var'_noTar = `var'_tar[_n+1] if county[_n+1] == "other" & fileyear == fileyear[_n+1]
}
gsort fileyear county
duplicates drop fileyear, force
drop county
sort fileyear
save `docket_ct', replace

********************************************************************************
* Small firm
********************************************************************************

use `"`pathdocket'Docket_firmSmCt.dta"'
keep county *Sm* fileyear filemonth
duplicates drop
drop if fileyear < 1990

foreach var of varlist *firmSmCty_csum {
bysort fileyear filemonth: egen temp = mean(`var') if county != "tarrant" & `var' != 0
rename temp `var'_oth
sort fileyear filemonth `var'_oth
by fileyear filemonth: carryforward `var'_oth, replace
sort fileyear filemonth `var'_oth
carryforward `var'_oth, replace
replace `var'_oth = 0 if `var'_oth == .
}
gen tarrantID = 1 if county == "tarrant"
replace tarrantID = 0 if tarrantID == .
gsort fileyear filemonth -tarrantID
duplicates drop fileyear filemonth, force

foreach var of varlist *firmSmCty_csum *firmSm_csum {
replace `var' = . if `var' == 0 & county != "tarrant"
sort fileyear filemonth `var'
by fileyear filemonth: carryforward `var',replace
sort fileyear filemonth `var'
carryforward `var',replace
replace `var' = 0 if `var' == .
}

drop county
duplicates drop

sort fileyear
merge fileyear using `docket_ct', update
tab _merge
drop _merge

drop if filemonth == .

sort fileyear filemonth
save `docket_sm', replace

********************************************************************************
* By firm
********************************************************************************

clear
use `"`pathdocket'Docket_firmCt.dta"'
replace county = lower(county)
duplicates drop
drop if fileyear < 1990

drop *_sum*
local suff = "reimburse unitization pooling rule86 rule37 rule38 fieldrule complaint enforce rule3738 plugging"
foreach pre in `suff' {
gen `pre'_firm_csum_oth = `pre'_firm_csum - `pre'_firmCty_csum if county == "tarrant"
sort firm fileyear filemonth `pre'_firm_csum_oth 
by firm fileyear filemonth: carryforward `pre'_firm_csum_oth , replace
replace `pre'_firm_csum_oth = 0 if `pre'_firm_csum_oth == .
}
duplicates drop
gen tarrantID = 1 if county == "tarrant"
replace tarrantID = 0 if tarrantID == .
gsort fileyear filemonth -tarrantID
duplicates drop firm fileyear filemonth, force

foreach var of varlist *firmCty_csum {
replace `var' = . if county != "tarrant"
sort firm fileyear filemonth `var'
by firm fileyear filemonth: carryforward `var',replace
sort firm fileyear filemonth `var'
by firm : carryforward `var',replace
replace `var' = 0 if `var' == .
}

foreach var of varlist *firm_csum {
replace `var' = . if `var' == 0
sort firm fileyear filemonth `var'
by firm fileyear filemonth: carryforward `var',replace
sort firm fileyear filemonth `var'
by firm: carryforward `var', replace
replace `var' = 0 if `var' == .
}
drop county
duplicates drop

sort fileyear filemonth
merge fileyear filemonth using `docket_sm', update
tab _merge
drop if _merge == 2
drop _merge
drop if firm == ""
duplicates drop

foreach var of varlist *firm_csum {
replace `var' = . if `var' == 0
sort firm fileyear filemonth `var'
by firm fileyear filemonth: carryforward `var',replace
sort firm fileyear filemonth `var'
by firm: carryforward `var',replace
replace `var' = 0 if `var' == .
}

rename firm firm_1 
rename fileyear instyear 
rename filemonth instmonth

drop firmNUM*
tab firm_small
drop firm_count

sort firm_1 instyear instmonth
save `docket',replace

********************************************************************************
* Merge future production
********************************************************************************

use `"`pathout'WellpadUpdate_permits&prod_04192018.dta"'

drop wellpadID
rename wellpadnew wellpadID
duplicates drop

drop rule37_hear rule37_nohear rule86fdate rule37_nohearfdate rule37_hearfdate rule38fdate rule37fdate cumgas cumoil first24oil first24gas first60oil first60gas
keep if regexm(county,"tarrant")

keep api operator wellpadID
sort api
save `prod',replace

use `"`pathprod'ProdDataMonthly_GIS_`shale'_loop.dta"'

drop wellpad*
keep api year month entity_id_prod api_maxmonthcount - api_pxcumgas
drop *max*

sort api
merge api using `prod', update replace
tab _merge
drop if _merge == 1
drop _merge

bysort wellpadID api: gen temp = _n
bysort wellpadID: egen apiToWellpad_count = sum(temp) if temp == 1
sort wellpadID apiToWellpad_count
by wellpadID: carryforward apiToWellpad_count, replace

bysort wellpadID year month: egen wellpad_cumoil = sum(api_cumoil)
bysort wellpadID year month: egen wellpad_cumgas = sum(api_cumgas)
bysort wellpadID year month: egen wellpad_pxcumoil = sum(api_pxcumoil)
bysort wellpadID year month: egen wellpad_pxcumgas = sum(api_pxcumgas)

rename year instyear 
rename month instmonth

gsort api -instyear -instmonth
duplicates drop api, force

sort api
save `prod',replace

********************************************************************************
* Lease observations
********************************************************************************

use  `"`pathlease'LeaseMergedTAD_04122018_att.dta"'

drop if account_num == .
drop if record == ""

replace insttype = lower(insttype)
drop if regexm(insttype,"assignment") | regexm(insttype,"seismic") | regexm(insttype,"other")
drop if regexm(insttype,"extension") | regexm(insttype,"option") | regexm(insttype,"amendment")

drop if instyear < 2003
drop if instyear >= 2014

replace termmonths = . if termmonths < 12
replace termmonths = . if termmonths > 60
destring royalty termmonths, replace force

replace grantee = lower(grantee)
replace grantor = lower(grantor)

replace firm_1 = "lv" if regexm(grantee,"^lv ")
replace firm_1 = "eagle" if regexm(grantee,"^eagle ")

compress record firm_1

********************************************************************************
* Merge to other variables
********************************************************************************

* TAD characteristics (including wells)
sort account_num
merge account_num using `aaaa', update
tab _merge
drop if _merge == 2
drop _merge

drop if api == ""

* Mean appraisal and transaction values
sort account_num instyear
merge account_num instyear using `aaaaval', update
tab _merge

gsort account_num -_merge
foreach var of varlist app_mean app_median app_mean04 app_median04 app_mean08 app_median08 {
by account_num: carryforward `var', replace
}
sort account_num instyear appValCpi
by account_num: carryforward appValCpi, replace
gsort abst_no -_merge
foreach var of varlist *abst_no_app* {
by abst_no: carryforward `var' if abst_no != "", replace
}
gsort api -_merge
foreach var of varlist *api_app* {
by api: carryforward `var' if api != "", replace
}
gsort wellpadID -_merge
foreach var of varlist *wellpadID_app* {
by wellpadID: carryforward `var' if wellpadID != ., replace
}
replace sub_num = basepidn if sub_num == ""
gsort sub_num -_merge
foreach var of varlist *sub_num_app* {
by sub_num: carryforward `var' if sub_num != "", replace
}
drop if _merge == 2
drop _merge

sort account_num instyear
merge account_num instyear using `tranval', update
tab _merge

gsort account_num -_merge
foreach var of varlist sale_mean sale_median sale_mean04 sale_median04 sale_mean08 sale_median08 {
by account_num: carryforward `var', replace
}
sort account_num instyear sale_amount_cpi
by account_num: carryforward sale_amount_cpi, replace
gsort abst_no -_merge
foreach var of varlist *abst_no_sale* {
by abst_no: carryforward `var' if abst_no != "", replace
}
gsort api -_merge
foreach var of varlist *api_sale* {
by api: carryforward `var' if api != "", replace
}
gsort wellpadID -_merge
foreach var of varlist *wellpadID_sale* {
by wellpadID: carryforward `var' if wellpadID != ., replace
}
replace sub_num = basepidn if sub_num == ""
gsort sub_num -_merge
foreach var of varlist *sub_num_sale* {
by sub_num: carryforward `var' if sub_num != "", replace
}
drop if _merge == 2
drop _merge

* Well info
sort api
merge api using `api', update
tab _merge
drop if _merge == 2
drop _merge

* Competition measures for reduced form
sort account_num instyear instmonth
merge account_num instyear instmonth using `competition', update
tab _merge
drop if _merge == 2
drop _merge

* Per well production and profit
sort api
merge api using `prod', update
tab _merge
drop if _merge == 2
drop _merge

* Price information at date lease is signed
sort instyear instmonth instday
merge instyear instmonth instday using `price'
tab _merge
drop if _merge == 2
drop _merge

********************************************************************************
* Merge to instruments
********************************************************************************

* Operator Characteristics

replace firm_1 = "arrington" if regexm(firm_1,"arrington")
replace firm_1 = "caffey" if regexm(firm_1,"caffey")
*replace firm_1 = "grande" if regexm(firm_1,"grand e")
replace firm_1 = "circle" if regexm(firm_1,"circle exp")
replace firm_1 = "cwc" if regexm(firm_1,"cwc ")
replace firm_1 = "kerr" if regexm(firm_1,"kerr ")
replace firm_1 = "prime" if regexm(firm_1,"prime ")
replace firm_1 = "range" if regexm(firm_1,"range ")
replace firm_1 = "tierra" if regexm(firm_1,"tierra ")
replace firm_1 = "woodstone" if regexm(firm_1,"woodstone ")

sort firm_1
merge firm_1 using `opdrill', update
tab _merge
drop if _merge == 2
drop _merge
replace operatorDum = 0 if operatorDum == .

* Distance to TCC
sort record instdate
merge record instdate using `leasedist', update
tab _merge
drop if _merge == 2
drop _merge

* OLD instruments
sort abstract instyear instmonth
merge abstract instyear instmonth using `nearbyInf'
tab _merge

gsort abstract instyear instmonth -_merge
foreach var of varlist permitByAbst_II_near - gas_tot_max_abst_near {
	replace `var' = . if `var' == 0
	by abstract: carryforward `var',replace
	replace `var' = 0 if `var' == .
}

drop if _merge == 2
drop _merge

sort abstract instyear instmonth
merge abstract instyear instmonth using `permitabst'
tab _merge

gsort abstract instyear instmonth -_merge
foreach var of varlist permitByAbst prodByAbst permitByAbst03 prodByAbst03 permitByAbst_I prodByAbst_I permitByAbst03_I prodByAbst03_I permitByAbst_II prodByAbst_II permitByAbst03_II prodByAbst03_II {
	replace `var' = . if `var' == 0
	by abstract: carryforward `var',replace
	replace `var' = 0 if `var' == .
}

drop if _merge == 2
drop _merge


* Firm level docket info
sort firm_1 instyear instmonth
merge firm_1 instyear instmonth using `docket', update
tab _merge

foreach suf in reimburse rule3738 rule37 rule38 rule86 plugging enforce pooling unitization complaint fieldrule {

replace `suf'_firmCty_csum = . if `suf'_firmCty_csum == 0
sort firm_1 instyear instmonth `suf'_firmCty_csum
by firm_1: carryforward `suf'_firmCty_csum if firm_1 != "", replace
replace `suf'_firmCty_csum = 0 if `suf'_firmCty_csum == .

replace `suf'_firm_csum = . if `suf'_firm_csum == 0
sort firm_1 instyear instmonth `suf'_firm_csum
by firm_1: carryforward `suf'_firm_csum if firm_1 != "", replace
replace `suf'_firm_csum = 0 if `suf'_firm_csum == .

replace `suf'_firm_csum_oth = . if `suf'_firm_csum_oth == 0
sort firm_1 instyear instmonth `suf'_firm_csum_oth
by firm_1: carryforward `suf'_firm_csum_oth if firm_1 != "", replace
replace `suf'_firm_csum_oth = 0 if `suf'_firm_csum_oth == .

replace `suf'_firmSmCty_csum = . if `suf'_firmSmCty_csum == 0
sort instyear instmonth `suf'_firmSmCty_csum
carryforward `suf'_firmSmCty_csum if firm_1 != "", replace
replace `suf'_firmSmCty_csum = 0 if `suf'_firmSmCty_csum == .

replace `suf'_firmSm_csum = . if `suf'_firmSm_csum == 0
sort instyear instmonth `suf'_firmSm_csum
carryforward `suf'_firmSm_csum if firm_1 != "", replace
replace `suf'_firmSm_csum = 0 if `suf'_firmSm_csum == .

replace `suf'_firmSmCty_csum_oth = . if `suf'_firmSmCty_csum_oth == 0
sort instyear instmonth `suf'_firmSmCty_csum_oth
carryforward `suf'_firmSmCty_csum_oth if firm_1 != "", replace
replace `suf'_firmSmCty_csum_oth = 0 if `suf'_firmSmCty_csum_oth == .
}

drop if _merge == 2
drop _merge

********************************************************************************

replace land_sqft = . if land_sqft == 0
sum land_sqft, det
replace land_sqft = . if land_sqft < 1000
replace land_sqft = . if land_sqft > r(p99)

********************************************************************************
* Delete the record account_num duplicates
* 	matching a few properties to a single lease

compress record firm_1 grantor* grantee*
duplicates drop account_num record situs_address, force
duplicates drop record situs_address prop_l*, force
duplicates drop record situs_address, force
replace situs_address = lower(situs_address)
capture confirm var temp
if !_rc {
drop temp
}
gen temp = regexs(0) if regexm(grantoraddress,situs_address)
gen counter = 1 if temp != ""
bysort record: egen tempdrop = sum(counter)

gsort record -situs_address
duplicates drop record if tempdrop != 0, force
drop temp counter tempdrop

gen temp1 = regexs(0) if regexm(situs_address,"^[0-9]+")
gen temp2 = regexs(0) if regexm(grantoraddress,"^[0-9]+")
gen counter = 1 if temp1 == temp2
bysort record: egen tempdrop = sum(counter)

gsort record -situs_address
duplicates drop record if tempdrop != 0, force
drop temp1 temp2 counter tempdrop

/*
duplicates tag account_num firmNUM, gen(t)
drop if t > 0
drop t
*/
********************************************************************************

replace city_name = "rural" if city_name == ""
gen rural = 1 if city_name == "rural"
replace rural = 0 if rural == .

bysort abstract: egen land_sqft_abst_avg = mean(land_sqft)

replace bonus = . if bonus == 0
replace extbonus = . if extbonus == 0
replace nytbonus = . if nytbonus == 0

replace extbonus = bonus if samebonus == 1 & extbonus == .
replace bonus = extbonus if samebonus == 1 & bonus == .

gen bonusfull = bonus
replace bonusfull = extbonus if bonusfull == .
sum bonusfull, det
replace bonusfull = . if bonusfull < r(p1)
replace bonusfull = . if bonusfull > r(p99)

gen bonus_acre = bonusfull*land_acres
sum bonus_acre, det
replace bonus_acre = . if bonus_acre < r(p1)
replace bonus_acre = . if bonus_acre > r(p99)


gen lateral_dist_inv = 1/lateral_dist
gen well_lateral_dist_inv = 1/well_lateral_dist
replace near_dist_pipe_inv = 0 if near_dist_pipe_inv == .
replace well_lateral_dist_inv = 0 if well_lateral_dist_inv == .
replace lateral_dist_inv = 0 if lateral_dist_inv == .

********************************************************************************

gen counter = 1
bysort wellpadID: egen totalsigned = sum(counter) if wellpadID != .
bysort wellpadID firmNUM: egen firmsigned = sum(counter) if wellpadID != .
bysort api: egen api_totalsigned = sum(counter) if api != ""

drop if api_totalsigned < 10
drop if totalsigned < 50

bysort api: gen api_ct = _n
bysort wellpadID: gen wellpad_ct = _n

replace firm_1 = "lv" if regexm(grantee,"^lv ")
replace firm_1 = "eagle" if regexm(grantee,"^eagle ")

drop firm_ct
bysort firm_1: egen firm_ct = sum(counter)
sum firm_ct, det 
replace firm_1 = "smallfirm" if firm_ct < r(p5)
replace firmNUM = 0 if firm_1 == "smallfirm"
tab firm_1
drop counter
* 4% of the sample is leased by small firms

********************************************************************************
* Time from lease to production calculations

split firstproddate,parse("-") gen(dat)
gen firstprodmonth = dat1 if length(dat1) == 2
replace firstprodmonth = dat2 if firstprodmonth == ""
destring firstprodmonth, replace
drop dat1 - dat3
tab firstprodmonth

gen fdate_inst = ym(instyear, instmonth)
gen fdate_prod = ym(firstprodyear, firstprodmonth)
drop if fdate_inst == .

gen leaseBeforeProd = 1 if fdate_inst <= fdate_prod  & api != ""
gen timeToProd = fdate_prod - fdate_inst
hist timeToProd if timeToProd >= 0

bysort api: egen first_lease = min(fdate_inst) if leaseBeforeProd == 1 & api != ""
bysort api: egen last_lease = max(fdate_inst) if leaseBeforeProd == 1  & api != ""
gen timeToLease = last_lease - first_lease

hist timeToLease if api_ct == 1
hist timeToLease if api_ct == 1 & api_totalsigned > 20

gen leaseMonthsAfterFirst = fdate_inst - first_lease
hist leaseMonthsAfterFirst if api_totalsigned > 20 & first_lease > 536 & leaseBeforeProd == 1
sum leaseMonthsAfterFirst if api_totalsigned > 20 & first_lease > 536 & leaseBeforeProd == 1, det
* takes about 2.5 years to assemble leases to permit, on average

********************************************************************************
* Before 2009 Sampling
********************************************************************************

gen signedBefore09 = 1 if fdate_inst < 588 & api != "" & leaseBeforeProd == 1
bysort api: egen api_signedBefore09 = sum(signedBefore09) if api != ""
bysort wellpadID: egen wellpad_signedBefore09 = sum(signedBefore09) if wellpadID != .

*gen diff_wp_signedBefore09 = totalsigned - wellpad_signedBefore09
gen frac_wp_signedBefore09 = wellpad_signedBefore09/totalsigned
*gen diff_api_signedBefore09 = api_totalsigned - api_signedBefore09
gen frac_api_signedBefore09 = api_signedBefore09/api_totalsigned

hist frac_wp_signedBefore09 if wellpad_ct == 1 & frac_wp_signedBefore09 > 0 
hist frac_api_signedBefore09 if api_ct == 1 & frac_api_signedBefore09 > 0

gen sampleFor2009 = 1 if frac_wp_signedBefore09 >= .80
tab sampleFor2009

********************************************************************************
* Expected royalty profit
********************************************************************************

sum contig_acres if api_ct == 1, det
replace contig_acres = . if contig_acres > r(p99)

replace land_acres = . if land_acres == 0
*replace land_sqft = land_acres*43560 if land_sqft == .
replace land_acres = land_sqft/43560 if land_acres == .

sum land_acres, det
replace land_acres = . if land_acres > r(p99)
replace land_acres = . if land_acres < r(p1)

bysort api: egen leased_acres = sum(land_acres) if api != ""
bysort wellpadID: egen leased_wp_acres = sum(land_acres) if wellpadID != .

gen land_acres_frac = land_acres/contig_acres
replace land_acres_frac = land_acres/leased_acres if land_acres_frac == .
sum land_acres_frac, det
replace land_acres_frac = . if land_acres_frac < r(p1)
replace land_acres_frac = . if land_acres_frac > r(p99)

replace api_pxcumoil = 0 if api_pxcumoil == .
gen exp_royalty_oil = api_pxcumoil*land_acres_frac
replace api_pxcumgas = 0 if api_pxcumgas == .
gen exp_royalty_gas = api_pxcumgas*land_acres_frac

sum api_cumgas if api_ct == 1 & api_cumgas != 0, det
*drop if api_cumgas > r(p99)

gen exp_royalty = exp_royalty_oil + exp_royalty_gas
hist exp_royalty if exp_royalty > 0
sum exp_royalty if exp_royalty > 0, det

gen log_exp_royalty = log(exp_royalty)
hist log_exp_royalty if exp_royalty > 0

replace bonus_acre = . if land_acres == .

********************************************************************************
* Sub in some wellpad and abst averages

drop api_ct abst_ct
bysort api: gen api_ct = _n
bysort abst_no: gen abst_ct = _n

replace api_pxcumgas = . if api_pxcumgas == 0
bysort wellpadID: egen api_pxcumgas_wp_med = median(api_pxcumgas) if api_pxcumgas != . & api_ct == 1 & api_months_produce < 12
bysort abst_no: egen api_pxcumgas_abst_med = median(api_pxcumgas) if api_pxcumgas != . & abst_ct == 1 & api_months_produce < 12
sort wellpadID api_pxcumgas_wp_med
by wellpadID: carryforward api_pxcumgas_wp_med, replace
sort abst_no api_pxcumgas_abst_med
by abst_no: carryforward api_pxcumgas_abst_med, replace

replace api_pxcumgas = api_pxcumgas_wp_med if api_pxcumgas == .
replace api_pxcumgas = api_pxcumgas_abst_med if api_pxcumgas == .
replace api_pxcumgas = 0 if api_pxcumgas == .

replace api_pxcumoil = . if api_pxcumoil == 0
bysort wellpadID: egen api_pxcumoil_wp_med = median(api_pxcumoil) if api_pxcumoil != . & api_ct == 1 & api_months_produce < 12
bysort abst_no: egen api_pxcumoil_abst_med = median(api_pxcumoil) if api_pxcumoil != . & abst_ct == 1 & api_months_produce < 12
sort wellpadID api_pxcumoil_wp_med
by wellpadID: carryforward api_pxcumoil_wp_med, replace
sort abst_no api_pxcumoil_abst_med
by abst_no: carryforward api_pxcumoil_abst_med, replace

replace api_pxcumoil = api_pxcumoil_wp_med if api_pxcumoil == .
replace api_pxcumoil = api_pxcumoil_abst_med if api_pxcumoil == .
replace api_pxcumoil = 0 if api_pxcumoil == .

replace exp_royalty_oil = api_pxcumoil*land_acres_frac
replace exp_royalty_gas = api_pxcumgas*land_acres_frac
replace exp_royalty = exp_royalty_oil + exp_royalty_gas
drop api_ct abst_ct

********************************************************************************
* Sub small firm values
********************************************************************************

foreach suf in reimburse rule3738 rule37 rule38 rule86 plugging enforce pooling unitization complaint fieldrule {
replace `suf'_firm_csum = `suf'_firmSm_csum  if firm_1 == "smallfirm"
replace `suf'_firm_csum = 0 if `suf'_firm_csum == .
drop `suf'_firmSm_csum
replace `suf'_firmCty_csum = `suf'_firmSmCty_csum  if firm_1 == "smallfirm"
replace `suf'_firmCty_csum = 0 if `suf'_firmCty_csum == .
drop `suf'_firmSmCty_csum
replace `suf'_firm_csum_oth = `suf'_firmSmCty_csum_oth if firm_1 == "smallfirm"
replace `suf'_firm_csum_oth = 0 if `suf'_firm_csum_oth == .
drop `suf'_firmSmCty_csum_oth
}

foreach var in op_prod_TCwells op_prodgas_TCwells op_proddir_TCwells op_prod_wells op_prodgas_wells op_proddir_wells op_prod_wells_2003 op_prodgas_wells_2003 op_proddir_wells_2003 {
replace `var' = `var'_sm if firm_1 == "smallfirm"
*drop `var'_sm
}
foreach var of varlist op_prod* {
sort firm_1 `var'
by firm_1: carryforward `var',replace
}
drop op_prod_TCwells_sm op_prodgas_TCwells_sm op_proddir_TCwells_sm op_prod_wells_sm op_prodgas_wells_sm op_proddir_wells_sm op_prod_wells_2003_sm op_prodgas_wells_2003_sm op_proddir_wells_2003_sm

foreach var of varlist rule37_yr - rule3738_yr_noTar {
sort instyear `var'
by instyear: carryforward `var',replace
}

********************************************************************************

keep account_num api wellpadID firm_1 firmNUM parcelIntersectsLateral land_acres land_sqft contig_acres insttype instdate termmonths royalty /*
*/ bonus extbonus instyear instmonth attributemerge_2017 nytsample - waterlease city_name api_* wellpad_* rule37 rule38 wellpad_ct /*
*/ record horizontal abst_no app_* *_app_* sale_* *_sale_* smallFirm operatorDum operatorLarge office_* lease_tcc* exp_* /*
*/ firmshare_clust firm_small rural - sampleFor2009 near_dist_pipe_inv near_dist_pipe well_lateral_dist lateral_dist well_dist near_dist_wellpad_inv surfaceuserest 

rename firm_small firm_small_docket
drop *lat_* *long_*
drop *_mean*
drop lease_tcc_sl_dist lease_tcc_man_dist lease_tcc_grp_dist lease_tcc_arl_dist office_tcc_sl_dist office_tcc_man_dist office_tcc_grp_dist office_tcc_arl_dist
drop sub_num_sale_med08 api_sale_med08 wellpadID_sale_med08 abst_no_sale_med08 sub_num_app_med08 api_app_med08 wellpadID_app_med08 abst_no_app_med08
drop group growcrops hazmat indemnity insurance groundwatercontam groundwaterprotect envmed escrow extpay disclaimerofrep attorneyfee attorneyatlaw attorney bonafideoffer champ communityassoc addendum armslength assignDevised jointlyprepare neg_bin noLitigation noise nonhomestead nosurfaceuse nowarranty oneTotwoExtract permineralacre pollution postprodcost royaltytopaytitle setback spec_warranty sublease substitute terminate_noroyal testing toplease toxic volgroup waterquality watersediment withoutdeduct 
drop api_signedBefore09 wellpad_signedBefore09 frac_wp_signedBefore09 frac_api_signedBefore09
drop *12mon*
drop offset nowateruse paydamage subsurfe_bin storagetanks assign_except assign_restrict
drop legalexpense lesseeDefends lessorDefends lessorNoWarranty muffler
drop developmentPlan defendtitlelessor defendtitle environment drillcore gasprice cercla
drop nolitigationlessor surfaceUseWaiver surfaceuserest transportcost
drop NoDefend damage cleanup freewater freshwaterprotect surfacecasing
drop api_lateral api_prod_maxmonthcount api_prod_cummonthcount wellpad_well api_well wellpad_lateral api_cummonthcount

gen traffic_bun = residentstreet if residentstreet == 1
replace traffic_bun = traffic if traffic != . & traffic_bun == .
tab traffic_bun
drop residentstreet traffic

replace parcelIntersectsLateral = 0 if parcelIntersectsLateral == .
rename parcelIntersectsLateral parcelIntLat

replace termmonths = . if termmonths > 100
replace termmonths = . if termmonths < 10

destring daysaftercess, replace
sum daysaftercess, det
replace daysaftercess = . if daysaftercess < r(p1)
replace daysaftercess = . if daysaftercess > r(p99)
sum daysaftercess, det

replace land_sqft = land_sqft/1000
replace abst_no_app_med = abst_no_app_med/1000
gen royalty_land = royalty*land_sqft

bysort wellpadID: gen temp = _n
bysort wellpadID: egen parcelToWP = max(temp)
drop temp
hist parcelToWP
bysort api: gen temp = _n
bysort api: egen parcelToAPI = max(temp)
drop temp
hist parcelToAPI

replace city_name = "westlake" if city_name == "trophy club"
replace city_name = "westworth village" if city_name == "westover hills"
replace city_name = "rural" if city_name == "pelican bay"
replace city_name = "arlington" if city_name == "pantego"
replace city_name = "rural" if city_name == "lakeside"
replace city_name = "rural" if city_name == "haslet"
replace city_name = "saginaw" if city_name == "blue mound"

gen counter = 1
bysort city_name: egen city_ct = sum(counter)
sum city_ct, det
replace city_name = "rural" if city_ct < r(p1)

replace  prodByAbst03_near = 0 if  prodByAbst03_near == .
replace  permitByAbst03_near = 0 if  permitByAbst03_near == .
replace  prodByAbst03_near = 0 if  prodByAbst03_near == .
replace  permitByAbst03_near = 0 if  permitByAbst03_near == .
foreach var of varlist op_prod* {
replace `var' = 0 if `var' == .
}

********************************************************************************
* Firm count variables

bysort wellpadID firm_1 instyear: gen ct = _n
bysort wellpadID instyear: egen firmcount_wp_yr = sum(ct) if ct == 1
sort wellpadID instyear firmcount_wp_yr
by wellpadID: carryforward firmcount_wp_yr, replace
drop ct

sort wellpadID firm_1 instyear instmonth instday
by wellpadID firm_1: gen ct = _n
sort wellpadID instyear instmonth instday
by wellpadID: gen temp = sum(ct) if ct == 1
sort wellpadID instyear instmonth instday temp
by wellpadID instyear instmonth instday: carryforward temp, replace
by wellpadID instyear instmonth : carryforward temp, replace
by wellpadID instyear  : carryforward temp, replace
by wellpadID : carryforward temp, replace
rename temp firmcount_wp_cum
drop ct

sort wellpadID firm_1 instyear instmonth instday
by wellpadID firm_1: gen ct = _n
bysort wellpadID instyear instmonth: egen tempsum = sum(ct) if ct == 1
by wellpadID: gen temp = sum(tempsum)
replace temp = . if temp == 0
sort wellpadID instyear instmonth temp
by wellpadID instyear instmonth : carryforward temp, replace
by wellpadID instyear  : carryforward temp, replace
by wellpadID : carryforward temp, replace
rename temp firmcount_wp_Mcum
drop ct tempsum

********************************************************************************
* Define lease bundles

local legal   = "forcemaj pugh verticalpugh insurindem_bun totreport_bun postprod_bun lesseeDefends"
local bads    = "subsurfease_bun nolitigationlessor freewater"
local surface = "surfdamage_bun nosurf_bun surfrest_bun setback"

drop legallease
gen legallease = 0 if leaseAttributeMerge != .
foreach var of varlist `legal' {
	replace legallease = legallease + 1 if `var' == 1
}
replace legallease = legallease/7

drop badslease
gen badslease = 0 if leaseAttributeMerge != .
foreach var of varlist `bads' {
	replace badslease = badslease + 1 if `var' == 1
}
replace badslease = badslease/3

drop surfacelease
gen surfacelease = 0 if leaseAttributeMerge != .
foreach var of varlist `surface' {
	replace surfacelease = surfacelease + 1 if `var' == 1
}
replace surfacelease = surfacelease/4

local full  = "environ_bun noise_bun freshwaterprotect surfacecasing compressionstation traffic_bun surfdamage_bun nosurf_bun surfrest_bun setback forcemaj pugh verticalpugh insurindem_bun totreport_bun postprod_bun lesseeDefends"
drop fulllease
gen fulllease = 0 if leaseAttributeMerge != .
foreach var of varlist `full' {
	replace fulllease = fulllease + 1 if `var' == 1
}
local full  = "subsurfease_bun nolitigationlessor freewater"
foreach var of varlist `full' {
	replace fulllease = fulllease - 1 if `var' == 1
}
replace fulllease = fulllease/17


save "`pathfile'/DataForJMP_Matching.dta", replace

